﻿--delete  from SERVER12.ANHCP.[dbo].[LD_HTC] where BUSINESS_DATE between '20151223' and '20151229'
--delete  from LD_HTC where BUSINESS_DATE between '20151223' and '20151229'
--delete from BAOLANH_MD_HTC where BUSINESS_DATE between '20151223' and '20151229'
--delete from SERVER12.ANHCP.[dbo].[BAOLANH_MD_HTC] where BUSINESS_DATE between '20151223' and '20151229'
--delete from SERVER12.ANHCP.[dbo].[LD_DISBURSEMENT_HTC] where DIST_DATE between '20151223' and '20151229'
--delete from LD_DISBURSEMENT_HTC where DIST_DATE between '20151223' and '20151229'
--delete from DOANH_SO_BAO_LANH_HTC where VALUE_DATE between '20151223' and '20151229'
--delete from SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC where VALUE_DATE between '20151223' and '20151229'

--delete from DU_NO_HTC where BUSINESS_DATE = '20151229'
--delete from SERVER12.ANHCP.DBO.DU_NO_HTC where BUSINESS_DATE ='20151229'
--delete from SERVER12.ANHCP.DBO.DU_BAOLANH_HTC where BUSINESS_DATE ='20151229'
--delete from DU_BAOLANH_HTC where BUSINESS_DATE ='20151229'
--select *  FROM SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB] WHERE FUND_PROGRAM LIKE '%VEAM%' and business_date >='20160301'   ----cust_id='2614454'
--select * from [SERVER16_ANHCP].[VPB_WHR2].[dbo].[T24CRD] WHERE FUND_PROGRAM LIKE '%VEAM%' and business_date >='20160301'

				--select max(business_date)  FROM SERVER16_ANHCP.[VPB_WHR2].[DBO].[CRB_ALL] A
				--select max(business_date)  FROM SERVER16_ANHCP.vpb_whr2.dbo.[T24CRD]
				--select max(business_date) FROM SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB]
				--select max(DIST_DATE) from SERVER74.BICDATA.DBO.LNTB_DISBURSEMENT
				--select max(business_date)  from SERVER16_ANHCP.[VPB_WHR2].[DBO].[TBL_LIMIT_ORG] 


--FUND_PROGRAM = ''TAI TRO DAI LY BAN VE CUA HHK''
--FUND_PROGRAM = 'TAI TRO CHUOI HTC'
----HTC CÓ MD TỔNG CATEGORY 28609 CÒN VNA KHÔNG CÓ MD TỔNG

--20151210-20151218
-----LẤY HỢP ĐỒNG của chương trình CHO VAY (GIẢI NGÂN)

	DECLARE @DATE_DT NVARCHAR (20)
	SET @DATE_DT='20160717'

	DECLARE @DATE_CT NVARCHAR (20)
	SET @DATE_CT='20160723'

PRINT('1')
EXEC('SELECT A.BUSINESS_DATE, A.COMPANY, A.CCY,A.GL,A.ID, A.TYPE,A.TERM, A.AMOUNT_LCY/(-1E9) AS AMOUNT
,A.CUST_ID,C.CUS_NAME,A.SECTOR, A.VALUE_DATE, A.MATURITDATE, B.NAME, ''REGION''+B.REGION AS REGION,
D.FUND_PROGRAM
INTO LD'+@DATE_DT+'_'+@DATE_CT+'
 FROM SERVER16_ANHCP.[VPB_WHR2].[DBO].[CRB_ALL] A
 LEFT JOIN [ANHCP].[CEO_PERFORMANCE].DBO.SME_BRANCHCODE B
ON A.COMPANY COLLATE DATABASE_DEFAULT = B.BRANCH
LEFT JOIN [SERVER74].[BICDATA].[dbo].[CUSTOMER] C
--SERVER16_ANHCP.VPB_WHR2.DBO.VPB_CUSTOMER C
ON A.CUST_ID=C.RECID
 join ( SELECT DISTINCT CONTRACT_NO, FUND_PROGRAM FROM SERVER16_ANHCP.vpb_whr2.dbo.[T24CRD]
WHERE BUSINESS_DATE = '''+@DATE_CT+'''
					--WHERE BUSINESS_DATE BETWEEN '''+@DATE_DT+''' AND '''+@DATE_CT+'''
			AND FUND_PROGRAM LIKE ''%VEAM%'') D
ON A.ID=D.CONTRACT_NO

					--WHERE A.BUSINESS_DATE BETWEEN '''+@DATE_DT+''' AND '''+@DATE_CT+'''
WHERE A.BUSINESS_DATE = '''+@DATE_CT+'''
----AND((A.SEGMENT<>''KHCN'' AND A.COMPANY NOT IN (SELECT BRANCH_CODE FROM [ANHCP].CEO_PERFORMANCE.DBO.BRANCHOFF))
----                        OR (A.SEGMENT IS NULL AND LEFT(A.SECTOR,1)>1))
and ((A.SEGMENT not in (''KHCN'',''HH'') 
and A.COMPANY not in (select branch_code from [ANHCP].CEO_PERFORMANCE.DBO.BRANCHOFF
 where branch_code <> ''VN0010289''))
              or (A.SEGMENT is null and LEFT(A.SECTOR,1)>1))

----------AND A.ID IN ( SELECT CONTRACT_NO FROM SERVER16_ANHCP.vpb_whr2.dbo.[T24CRD]
----------WHERE A.BUSINESS_DATE = '''+@DATE_CT+'''
----------					--WHERE BUSINESS_DATE BETWEEN '''+@DATE_DT+''' AND '''+@DATE_CT+'''
----------			AND FUND_PROGRAM LIKE ''%VEAM%'')
AND A.GL <>''3941'' ')--------------------------------------------------------- LOẠI BỎ DÒNG HẠCH TOÁN LÃI, CHỈ LẤY GỐC

PRINT('2')
EXEC( 'INSERT [LD_HTC]
SELECT * FROM LD'+@DATE_DT+'_'+@DATE_CT+'')

PRINT('3')
EXEC( 'INSERT SERVER12.ANHCP.[dbo].[LD_HTC]
SELECT * FROM LD'+@DATE_DT+'_'+@DATE_CT+'')

--delete LD_HTC
--delete SERVER12.ANHCP.[dbo].[LD_HTC]

----------- BẢO LÃNH TỔNG HỢP = MD1&MD2 (Chi tiết xem bên dưới)
PRINT('4')
	EXEC('SELECT A.BUSINESS_DATE, A.COMPANY, A.CCY,A.ID, A.AMOUNT_LCY/(-1E9) AS AMOUNT,
				A.CUST_ID,A.SECTOR, A.VALUE_DATE, A.MATURITDATE,C.CUS_NAME, B.NAME AS BRANCH_NAME, ''REGION ''+B.REGION AS REGION 
				,D.FUND_PROGRAM
				INTO MD1
				       FROM SERVER16_ANHCP.[VPB_WHR2].[DBO].[CRB_ALL] A
						LEFT JOIN [ANHCP].[CEO_PERFORMANCE].DBO.SME_BRANCHCODE B
						ON A.COMPANY COLLATE DATABASE_DEFAULT = B.BRANCH
						LEFT JOIN SERVER16_ANHCP.VPB_WHR2.DBO.VPB_CUSTOMER C
						ON A.CUST_ID=C.RECID
						JOIN  ( SELECT DISTINCT MD_ID, FUND_PROGRAM FROM SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB]
							WHERE BUSINESS_DATE = '''+@DATE_CT+'''
									AND FUND_PROGRAM LIKE ''%VEAM%'') D
									ON A.ID=D.MD_ID
				WHERE A.GL IN (''9210'',''9220'',''9260'',''9270'',''9280'') 
					AND A.BUSINESS_DATE = '''+@DATE_CT+'''
						--AND A.BUSINESS_DATE BETWEEN '''+@DATE_DT+''' AND '''+@DATE_CT+'''
							--------AND ((A.SEGMENT<>''KHCN'' AND A.COMPANY NOT IN (SELECT BRANCH_CODE FROM [ANHCP].CEO_PERFORMANCE.DBO.BRANCHOFF))
							--------			OR (A.SEGMENT IS NULL AND LEFT(A.SECTOR,1)>1))

and ((A.SEGMENT not in (''KHCN'',''HH'') 
and A.COMPANY not in (select branch_code from [ANHCP].CEO_PERFORMANCE.DBO.BRANCHOFF where branch_code <> ''VN0010289''))
or (A.SEGMENT is null and LEFT(A.SECTOR,1)>1))


						--AND A.ID IN ( SELECT MD_ID FROM SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB]
						--			--WHERE BUSINESS_DATE BETWEEN '''+@DATE_DT+''' AND '''+@DATE_CT+'''
						--	WHERE BUSINESS_DATE = '''+@DATE_CT+'''
						--			AND FUND_PROGRAM LIKE ''%VEAM%'') 
									')

--select top 1000 * from SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB]
PRINT('5')
		EXEC('SELECT A.BUSINESS_DATE, A.CO_CODE, A.CCY,A.MD_ID AS ID, AMOUNT*D.MID_RATE/1E9 AS AMOUNT,
				A.CUST_ID,A.SECTOR, A.VALUE_DATE, A.MATURITY_DATE,A.CUS_NAME,
		Y.NAME AS BRANCH_NAME, ''REGION ''+Y.REGION AS REGION 
		,FUND_PROGRAM
		INTO MD2
				FROM ( SELECT A.*, B.CUS_NAME FROM SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB] A
				LEFT JOIN [SERVER16_ANHCP].VPB_WHR2.DBO.VPB_CUSTOMER B
				ON A.CUST_ID = B.RECID
				WHERE CATEGORY = ''28609'' AND FUND_PROGRAM LIKE ''%VEAM%''
								AND BUSINESS_DATE = '''+@DATE_CT+''') A
				--AND BUSINESS_DATE BETWEEN '''+@DATE_DT+''' AND '''+@DATE_CT+''') A
				LEFT JOIN [ANHCP].[CEO_PERFORMANCE].DBO.SME_BRANCHCODE Y
				ON A.CO_CODE COLLATE DATABASE_DEFAULT = Y.BRANCH
					LEFT JOIN [SERVER16_ANHCP].[VPB_WHR2].[dbo].[FOCURR_SAVE] D
          ON A.BUSINESS_DATE=D.SAVE_DATE AND A.CCY=D.CODE')

		  PRINT('6.1')
		EXEC('SELECT A.BUSINESS_DATE, A.CO_CODE, A.CCY,A.MD_ID AS ID, AMOUNT*D.MID_RATE/1E9 AS AMOUNT,
				A.CUST_ID,A.SECTOR, A.VALUE_DATE, A.MATURITY_DATE,A.CUS_NAME,
		Y.NAME AS BRANCH_NAME, ''REGION ''+Y.REGION AS REGION
		,FUND_PROGRAM 
		INTO MD3
				FROM ( SELECT A.*, B.CUS_NAME FROM SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB] A
				LEFT JOIN [SERVER16_ANHCP].VPB_WHR2.DBO.VPB_CUSTOMER B
				ON A.CUST_ID = B.RECID
				WHERE  FUND_PROGRAM IN (''UU DAI VEAM MOTOR'')
								AND BUSINESS_DATE = '''+@DATE_CT+''') A
				--AND BUSINESS_DATE BETWEEN '''+@DATE_DT+''' AND '''+@DATE_CT+''') A
				LEFT JOIN [ANHCP].[CEO_PERFORMANCE].DBO.SME_BRANCHCODE Y
				ON A.CO_CODE COLLATE DATABASE_DEFAULT = Y.BRANCH
					LEFT JOIN [SERVER16_ANHCP].[VPB_WHR2].[dbo].[FOCURR_SAVE] D
          ON A.BUSINESS_DATE=D.SAVE_DATE AND A.CCY=D.CODE')


PRINT('6')
EXEC ('SELECT * INTO BAOLANH_MD_'+@DATE_DT+'_'+@DATE_CT+'
FROM MD1
UNION 
SELECT * FROM MD2
UNION 
SELECT * FROM MD3')
PRINT('7')
EXEC('INSERT [BAOLANH_MD_HTC]
SELECT * FROM BAOLANH_MD_'+@DATE_DT+'_'+@DATE_CT+'')
PRINT('8')
EXEC('INSERT SERVER12.ANHCP.[dbo].[BAOLANH_MD_HTC]
SELECT * FROM BAOLANH_MD_'+@DATE_DT+'_'+@DATE_CT+'')

--========================================

--select *,FUND_PROGRAM='Tai tro chuoi HTC'
--into BAOLANH_MD_HTC_1  from BAOLANH_MD_HTC

--select * from BAOLANH_MD_HTC where cust_id='2614454'
--select * from SERVER12.ANHCP.[dbo].[BAOLANH_MD_HTC] where cust_id='2614454'


--delete BAOLANH_MD_HTC
--select * from SERVER12.ANHCP.[dbo].[BAOLANH_MD_HTC] order by value_date desc

--SELECT * INTO BAOLANH_MD 
--FROM BAOLANH_MD_20151101_20151130 -----------------------------------insert vào 1 bảng tổng hợp

----LẤY DOANH SỐ GIẢI NGÂN THEO CHUỐI THÁNG, VÙNG, TRUNG TÂM
PRINT('9')
EXEC('SELECT A.*,B.DIST_DATE,B.BAL_QD/1E9 AS BAL_QD,B.SEGMENT 
INTO LD_DISBURSEMENT_'+@DATE_DT+'_'+@DATE_CT+'
FROM LD'+@DATE_DT+'_'+@DATE_CT+' A 
LEFT JOIN SERVER74.BICDATA.DBO.LNTB_DISBURSEMENT B
ON A.ID = B.ACCTNO 
WHERE B.DIST_DATE BETWEEN '''+@DATE_DT+''' AND '''+@DATE_CT+''' 
and A.BUSINESS_DATE='''+@DATE_CT+'''')


PRINT('10')
EXEC('INSERT SERVER12.ANHCP.[dbo].[LD_DISBURSEMENT_HTC]
SELECT [BUSINESS_DATE]
      ,[COMPANY]
      ,[CCY]
      ,[GL]
      ,[ID]
      ,[TYPE]
      ,[TERM]
      ,[AMOUNT]
      ,[CUST_ID]
      ,[CUS_NAME]
      ,[SECTOR]
      ,[VALUE_DATE]
      ,[MATURITDATE]
      ,[NAME]
      ,[REGION]
      ,[DIST_DATE]
      ,[BAL_QD]
      ,[SEGMENT]
	  , [DIST_DATE] as TO_DIST_DATE
      ,[FUND_PROGRAM]
FROM LD_DISBURSEMENT_'+@DATE_DT+'_'+@DATE_CT+' A
--LEFT JOIN DU_NO_HTC B
--ON A.CUST_ID =B.CUSTOMER_ID 
')

PRINT('11')
EXEC('INSERT [LD_DISBURSEMENT_HTC]
SELECT [BUSINESS_DATE]
      ,[COMPANY]
      ,[CCY]
      ,[GL]
      ,[ID]
      ,[TYPE]
      ,[TERM]
      ,[AMOUNT]
      ,[CUST_ID]
      ,[CUS_NAME]
      ,[SECTOR]
      ,[VALUE_DATE]
      ,[MATURITDATE]
      ,[NAME]
      ,[REGION]
      ,[DIST_DATE]
      ,[BAL_QD]
      ,[SEGMENT]
	  , [DIST_DATE] as TO_DIST_DATE
      ,[FUND_PROGRAM]
FROM LD_DISBURSEMENT_'+@DATE_DT+'_'+@DATE_CT+' A
--LEFT JOIN DU_NO_HTC B
--ON A.CUST_ID =B.CUSTOMER_ID
')------ căn cứ vào dist_date để xem xét doanh số giải ngân


--delete SERVER12.ANHCP.[dbo].[LD_DISBURSEMENT_HTC]
--delete LD_DISBURSEMENT_HTC

--select * from SERVER12.ANHCP.[dbo].[LD_DISBURSEMENT_HTC] order by from_dist_date desc

----LẤY DOANH SỐ BẢO LÃNH THEO CHUỐI THÁNG, VÙNG, TRUNG TÂM
PRINT('12')
EXEC ('SELECT  BUSINESS_DATE,MD_ID,CUST_ID,C.CUS_NAME, VALUE_DATE,MUC_DICH,CUS_SEGMENT,ORG_AMOUNT*MID_RATE AS AMOUNT_QD,
 BRANCH_NAME_SME,ZONE_ID_SME,CO_CODE,CATEGORY,FUND_PROGRAM 
 INTO MD_D
  FROM SERVER16_ANHCP.[VPB_WHR2].[DBO].[MD_RPT_DAILY_VPB] A
  LEFT JOIN  SERVER16_ANHCP.VPB_WHR2.DBO.FOCURR_SAVE B
  ON A.CCY=B.CODE
  LEFT JOIN SERVER74.BICDATA.DBO.CUSTOMER C
  ON A.CUST_ID=C.RECID
  LEFT JOIN SERVER74.BICDATA.DBO.BRANCH_CODE E
  ON A.CO_CODE=E.BRANCH_ID
WHERE SAVE_DATE='''+@DATE_CT+'''  ----------------------------------------CHOT NGAY CUOI THANG 
  AND VALUE_DATE BETWEEN '''+@DATE_DT+''' AND '''+@DATE_CT+''' 
	AND MD_ID IN (SELECT ID FROM BAOLANH_MD_'+@DATE_DT+'_'+@DATE_CT+')')
PRINT('13')
	EXEC('SELECT * ,ROW_NUMBER() OVER( PARTITION BY MD_ID ORDER BY BUSINESS_DATE) AS RN
INTO MD_D_1
FROM MD_D
ORDER BY BUSINESS_DATE')

PRINT('14')
	EXEC('SELECT [BUSINESS_DATE]
      ,[MD_ID]
      ,[CUST_ID]
	  ,CUS_NAME
      ,[VALUE_DATE]
      ,[MUC_DICH]
      ,[CUS_SEGMENT]
      ,[AMOUNT_QD]/1E9  AS AMOUNT_QD
      ,[BRANCH_NAME_SME]
      ,[ZONE_ID_SME]
      ,[CO_CODE]
      ,[CATEGORY]
      ,[FUND_PROGRAM]
      ,[RN] 
	   INTO MD_FINAL
FROM MD_D_1
WHERE RN=1 ')

--UPDATE MD_FINAL
--SET BUSINESS_DATE='20151130'------------------------------UPDATE NGAY BÁO CÁO CUỐI THÁNG NẾU INSERT VÀO CÙNG 1 BẢNG TỔNG HỢP

PRINT('15')
EXEC('SELECT *,ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY BUSINESS_DATE) AS COUNT_CIF 
INTO MD_FINAL_1
 FROM MD_FINAL')
PRINT('16')
EXEC('UPDATE MD_FINAL_1
 SET COUNT_CIF=0
 WHERE COUNT_CIF>1')

PRINT('17')
EXEC('UPDATE MD_FINAL_1
SET ZONE_ID_SME=''CHUA PHAN BO''
WHERE ZONE_ID_SME IS NULL')

PRINT('18')
EXEC('
INSERT DOANH_SO_BAO_LANH_HTC
SELECT * FROM MD_FINAL_1')
PRINT('19')
EXEC('
INSERT SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC
SELECT * , VALUE_DATE AS TO_VALUE_DATE FROM MD_FINAL_1
WHERE CATEGORY=''28609'' ')------ căn cứ vào value_date để xem phát sinh doanh số


--select * from SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC where fund_program like '%veam%'  DOANH_SO_BAO_LANH_HTC order by from_value_date desc


--delete DOANH_SO_BAO_LANH_HTC
--delete SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC

---- DƯ NỢ THỜI ĐIỂM THEO TỪNG CHUỖI, VÙNG, TRUNG TÂM
PRINT('20')
EXEC('SELECT BUSINESS_DATE, CUSTOMER_ID, CUSTOMER_NAME, E.BRANCH_NAME_SME, CONTRACT_NO, CURRENCY, 
LDPD_AMT*MID_RATE/1E9 AS AMOUNT, CUS_SEGMENT, FUND_PROGRAM, E.ZONE_ID_SME
INTO DU_NO_HTC_'+@DATE_CT+'
 FROM SERVER16_ANHCP.VPB_WHR2.DBO.[T24CRD] A
   LEFT JOIN  SERVER16_ANHCP.VPB_WHR2.DBO.FOCURR_SAVE B
  ON A.CURRENCY=B.CODE
AND A.BUSINESS_DATE=B.SAVE_DATE
LEFT JOIN SERVER74.BICDATA.DBO.BRANCH_CODE E
  ON A.BRANCH_CODE=E.BRANCH_ID
WHERE CONTRACT_NO IN (SELECT ID FROM LD'+@DATE_DT+'_'+@DATE_CT+')
AND BUSINESS_DATE='''+@DATE_CT+''' ') ----------------------------------------------THOI DIEM BÁO CÁO 
PRINT('21')
EXEC('INSERT DU_NO_HTC
SELECT * FROM DU_NO_HTC_'+@DATE_CT+'')
PRINT('22')
EXEC('INSERT SERVER12.ANHCP.DBO.DU_NO_HTC
SELECT * FROM DU_NO_HTC_'+@DATE_CT+'')

--select top 1 * from DU_NO_HTC
--select top 1 * from SERVER12.ANHCP.DBO.DU_NO_HTC
--delete DU_NO_HTC
--delete SERVER12.ANHCP.DBO.DU_NO_HTC
---- DƯ BẢO LÃNH THEO TỪNG CHUỖI, VÙNG, TRUNG TÂM
PRINT('23')
EXEC('SELECT *  INTO DU_BL_'+@DATE_CT+'
FROM BAOLANH_MD_'+@DATE_DT+'_'+@DATE_CT+' --------- amount_lcy là dư nợ đã quy đổi VND, fcy là dư nợ nguyên tệ 
WHERE BUSINESS_DATE='''+@DATE_CT+''' ')
PRINT('24')
EXEC('INSERT SERVER12.ANHCP.DBO.DU_BAOLANH_HTC
SELECT * FROM DU_BL_'+@DATE_CT+'')
PRINT('25')
EXEC('INSERT DU_BAOLANH_HTC
SELECT * FROM DU_BL_'+@DATE_CT+'')
PRINT('26')
EXEC('DROP TABLE LD'+@DATE_DT+'_'+@DATE_CT+'')
EXEC('DROP TABLE MD1')
EXEC('DROP TABLE MD2')
EXEC('DROP TABLE MD3')
EXEC('DROP TABLE BAOLANH_MD_'+@DATE_DT+'_'+@DATE_CT+'')
EXEC('DROP TABLE LD_DISBURSEMENT_'+@DATE_DT+'_'+@DATE_CT+'')
EXEC('DROP TABLE MD_D')
EXEC('DROP TABLE MD_D_1')
EXEC('DROP TABLE MD_FINAL')
EXEC('DROP TABLE MD_FINAL_1')
EXEC('DROP TABLE DU_NO_HTC_'+@DATE_CT+'')
EXEC('DROP TABLE DU_BL_'+@DATE_CT+'')

--SELECT * FROM SERVER12.ANHCP.DBO.DU_BAOLANH_HTC

 ---------------------------------------------------------------------------------------
 ----=======********HẠN MỨC KHÁCH HÀNG THAM GIA TÀI TRỢ CHUỖI HTC
 
  ----1. BẢO LÃNH

  EXEC('SELECT *,
(B.MID_RATE* 
(CASE WHEN [INTERNAL_AMOUNT] LIKE''%B%''THEN REPLACE([INTERNAL_AMOUNT],''B'',''000000000'')
WHEN INTERNAL_AMOUNT LIKE''%M%''THEN REPLACE([INTERNAL_AMOUNT],''M'',''000000'') 
ELSE [INTERNAL_AMOUNT] END))/1E9 AS [INTERNAL_AMOUNT_QD],
REVOLVING = CASE WHEN ID LIKE''%.0050000%'' THEN ''Y''
WHEN ID LIKE ''%.0060000%'' THEN ''N'' END
      INTO LIMIT_ORG_1
  FROM SERVER16_ANHCP.[VPB_WHR2].[DBO].[TBL_LIMIT_ORG] A
  LEFT JOIN [SERVER16_ANHCP].[VPB_WHR2].DBO.FOCURR_SAVE B
ON A.LIMIT_CURRENCY=B.CODE
AND B.SAVE_DATE ='''+@DATE_CT+'''
  WHERE BUSINESS_DATE='''+@DATE_CT+'''  --THAY NGAY--
  AND LIABILITY_NUMBER IN (SELECT DISTINCT CUST_ID FROM SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC
  WHERE FUND_PROGRAM LIKE ''%veam%'')
  AND (ID LIKE''%.0050000%''  OR ID LIKE ''%.0060000%''  ------ CHỈ THEO DÕI CÁC HẠN MỨC TUẦN HOÀN
  ) ')

--SELECT *  FROM SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC A

--SELECT TOP 10 * FROM SERVER16_ANHCP.[VPB_WHR2].[DBO].[MD_RPT_DAILY_VPB]

EXEC('SELECT DISTINCT CUST_ID, DATE='''+@DATE_CT+''', 
C.CUS_NAME, D.ZONE_ID_SME, D.BRANCH_NAME_SME, FUND_PROGRAM, [INTERNAL_AMOUNT_QD] AS LIMIT_AMOUNT, REVOLVING into LIMIT_ORG_2
FROM SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC A
LEFT JOIN (SELECT LIABILITY_NUMBER, REVOLVING, SUM([INTERNAL_AMOUNT_QD]) AS [INTERNAL_AMOUNT_QD] FROM LIMIT_ORG_1 GROUP BY LIABILITY_NUMBER, REVOLVING) B
ON A.CUST_ID=B.LIABILITY_NUMBER
LEFT JOIN [SERVER74].[BICDATA].[dbo].[CUSTOMER] C
ON A.CUST_ID=C.RECID
LEFT JOIN [SERVER74].[BICDATA].[dbo].[BRANCH_CODE] D
ON A.CO_CODE=D.BRANCH_ID
WHERE FUND_PROGRAM LIKE ''%veam%'' ')


EXEC('INSERT LIMIT_ORG_TTTM_CHUOI_VNA
select CUST_ID, convert(varchar(8), DATE, 112), CUS_NAME, ZONE_ID_SME, BRANCH_NAME_SME, FUND_PROGRAM,
 LIMIT_AMOUNT, REVOLVING,  HINH_THUC_TAI_TRO=''BAO LANH'' 
from LIMIT_ORG_2 A')

--EXEC('INSERT SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG ----------------BẢNG TỔNG HỢP CÁC CHUỖI
--select * from LIMIT_ORG_2 ')

EXEC('DROP TABLE LIMIT_ORG_1')


-----2. CHO VAY


  EXEC('SELECT *,
(B.MID_RATE* 
(CASE WHEN [INTERNAL_AMOUNT] LIKE''%B%''THEN REPLACE([INTERNAL_AMOUNT],''B'',''000000000'')
WHEN INTERNAL_AMOUNT LIKE''%M%''THEN REPLACE([INTERNAL_AMOUNT],''M'',''000000'') 
ELSE [INTERNAL_AMOUNT] END))/1E9 AS [INTERNAL_AMOUNT_QD],
REVOLVING = CASE WHEN ID LIKE''%.0050000%'' THEN ''Y''
WHEN ID LIKE ''%.0060000%'' THEN ''N'' END
      INTO LIMIT_ORG_1
  FROM SERVER16_ANHCP.[VPB_WHR2].[DBO].[TBL_LIMIT_ORG] A
  LEFT JOIN [SERVER16_ANHCP].[VPB_WHR2].DBO.FOCURR_SAVE B
ON A.LIMIT_CURRENCY=B.CODE
AND B.SAVE_DATE ='''+@DATE_CT+'''
  WHERE BUSINESS_DATE='''+@DATE_CT+'''  --THAY NGAY--
  AND LIABILITY_NUMBER IN (SELECT DISTINCT CUSTOMER_ID FROM SERVER12.ANHCP.DBO.DU_NO_HTC
  WHERE FUND_PROGRAM LIKE ''%veam%'')
  AND (ID LIKE''%.0050000%'' OR ID LIKE ''%.0060000%''
  ) ')

--SELECT *  FROM SERVER12.ANHCP.DBO.DU_NO_HTC A WHERE CUSTOMER_ID='2004567'

EXEC('SELECT DISTINCT CUSTOMER_ID, DATE='''+@DATE_CT+''', 
C.CUS_NAME, ZONE_ID_SME, BRANCH_NAME_SME, FUND_PROGRAM, [INTERNAL_AMOUNT_QD] AS LIMIT_AMOUNT, REVOLVING into LIMIT_ORG_3
FROM SERVER12.ANHCP.DBO.DU_NO_HTC A
LEFT JOIN (SELECT LIABILITY_NUMBER, REVOLVING, SUM([INTERNAL_AMOUNT_QD]) AS [INTERNAL_AMOUNT_QD] FROM LIMIT_ORG_1 GROUP BY LIABILITY_NUMBER, REVOLVING) B
ON A.CUSTOMER_ID=B.LIABILITY_NUMBER
LEFT JOIN [SERVER74].[BICDATA].[dbo].[CUSTOMER] C
ON A.CUSTOMER_ID=C.RECID
WHERE FUND_PROGRAM LIKE ''%veam%'' ')


EXEC('INSERT LIMIT_ORG_TTTM_CHUOI_VNA
select CUSTOMER_ID, convert(varchar(8), DATE, 112), CUS_NAME, ZONE_ID_SME, BRANCH_NAME_SME, FUND_PROGRAM,
 LIMIT_AMOUNT, REVOLVING, HINH_THUC_TAI_TRO=''CHO VAY'' 
from LIMIT_ORG_3 A')


EXEC('INSERT SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG ----------------BẢNG TỔNG HỢP CÁC CHUỖI
select CUST_ID, convert(varchar(8), DATE, 112), CUS_NAME, ZONE_ID_SME, BRANCH_NAME_SME, FUND_PROGRAM,
 LIMIT_AMOUNT, REVOLVING, convert(varchar(8), DATE, 112) from LIMIT_ORG_2
union
select CUSTOMER_ID, convert(varchar(8), DATE, 112), CUS_NAME, ZONE_ID_SME, BRANCH_NAME_SME, FUND_PROGRAM,
 LIMIT_AMOUNT, REVOLVING, convert(varchar(8), DATE, 112) from LIMIT_ORG_3 ')

EXEC('DROP TABLE LIMIT_ORG_1
DROP TABLE LIMIT_ORG_2
DROP TABLE LIMIT_ORG_3')


-------------------test & 3 KH hiện đang bị hệ thống ghi nhận nhầm

--select * from SERVER12.ANHCP.[dbo].[LD_HTC] where fund_program like'%veam%'


--select * from SERVER12.ANHCP.[dbo].[LD_DISBURSEMENT_HTC] where fund_program like'%veam%'

--select top 1 * from [SERVER16_ANHCP].[VPB_WHR2].[dbo].[T24CRD]

--SELECT BUSINESS_DATE, CUSTOMER_ID, CUSTOMER_NAME, CONTRACT_NO, CURRENCY, 
--VALUE_DATE, MATURITY_DATE, LDPD_AMT, A.BRANCH_CODE, B.BRANCH_NAME_SME, B.ZONE_ID_SME,
--FUND_PROGRAM  FROM [SERVER16_ANHCP].[VPB_WHR2].[DBO].[T24CRD] A
--LEFT JOIN [SERVER74].[BICDATA].[DBO].[BRANCH_CODE] B
--ON A.BRANCH_CODE=B.BRANCH_ID
--WHERE CUSTOMER_ID IN ('2632654',
--'2655123',
--'2737205')
--AND BUSINESS_DATE ='20160428'



--'check'
--select min(value_date) from SERVER12.ANHCP.[dbo].[BAOLANH_MD_HTC]  --------2015-06-30


--select min(value_date) FROM SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB] ------2015-05-16
--where  FUND_PROGRAM like '%htc%'

--SELECT min(value_date) FROM SERVER16_ANHCP.vpb_whr2.dbo.[T24CRD]
--where fund_program like '%HTC%'


--select min(value_date) from SERVER12.ANHCP.[dbo].[BAOLANH_MD_HTC]
------ where fund_program like '%HTC%'




--select * from SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG where fund_program like '%HTC%' order by from_date desc

------- UPDATE BRANCH_NAME_SME VÀ ZONE_ID_SME
--UPDATE SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG
--B.BRANCH_ID='VN0010123' ----- VPB THU DUC


--select * from SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG





--SELECT * FROM SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG
-----KH HTC

-----dư nợ

--SELECT distinct customer_id, FUND_PROGRAM  into ##c FROM SERVER16_ANHCP.vpb_whr2.dbo.[T24CRD]
--            WHERE  FUND_PROGRAM IN('TAI TRO CHUOI HT', 'Uu dai HTC') and year(business_date)='2015'


--			SELECT distinct cust_id, FUND_PROGRAM into ##d FROM SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB]
--									WHERE  FUND_PROGRAM IN ('Uu dai HTC', 'TAI TRO CHUOI HTC') and year(business_date)='2015'

--									select * into ##e from ##c union all select * from ##d


--									select a.*, b.cus_name from ##e a
--									left join [SERVER74].[BICDATA].[dbo].[CUSTOMER] b
--									on a.customer_id=b.recid


--delete SERVER12.ANHCP.DBO.DU_BAOLANH_HTC
--delete DU_BAOLANH_HTC


------- TÀI TRỢ CHO END USER 

--SELECT  DISTINCT FUND_PROGRAM  FROM SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB] WHERE BUSINESS_DATE='20160109'

--SELECT DISTINCT FUND_PROGRAM FROM SERVER16_ANHCP.vpb_whr2.dbo.[T24CRD] WHERE BUSINESS_DATE='20160109'




--select sum(amount) from SERVER12.ANHCP.DBO.DU_BAOLANH_HTC

----Trong đó:
----LẤY HỢP ĐỒNG BẢO LÃNH
---- LẤY HỢP ĐỒNG BẢO LÃNH -----MD1



--SELECT A.BUSINESS_DATE, A.COMPANY, A.CCY,A.GL,A.ID, A.TYPE,A.TERM, A.AMOUNT_LCY/(-1E9) AS AMOUNT,
--A.CUST_ID,A.SECTOR, A.VALUE_DATE, A.MATURITDATE,C.CUS_NAME, B.NAME, 'REGION'+B.REGION AS REGION 
--	FROM SERVER16_ANHCP.[VPB_WHR2].[DBO].[CRB_ALL] A

--	LEFT JOIN [ANHCP].[CEO_PERFORMANCE].DBO.SME_BRANCHCODE B
--	ON A.COMPANY COLLATE DATABASE_DEFAULT = B.BRANCH

--	LEFT JOIN SERVER16_ANHCP.VPB_WHR2.DBO.VPB_CUSTOMER C
--	ON A.CUST_ID=C.RECID

--WHERE A.GL IN ('9210','9220','9260','9270','9280') 
--	AND A.BUSINESS_DATE = '20151130'
--		AND ((A.SEGMENT<>'KHCN' AND A.COMPANY NOT IN (SELECT BRANCH_CODE FROM [ANHCP].CEO_PERFORMANCE.DBO.BRANCHOFF))
--					OR (A.SEGMENT IS NULL AND LEFT(A.SECTOR,1)>1))

--	AND A.ID IN ( SELECT MD_ID FROM SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB]
--				WHERE BUSINESS_DATE = '20151130' 
--				AND FUND_PROGRAM = 'TAI TRO CHUOI HTC')

				----------BẢO LÃNH TỔNG CATEGORY 28609---MD2

--SELECT X.*, Y.NAME
--FROM ( SELECT A.*, B.CUS_NAME FROM SERVER16_ANHCP.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB] A
--LEFT JOIN [SERVER16_ANHCP].VPB_WHR2.DBO.VPB_CUSTOMER B
--ON A.CUST_ID = B.RECID
--WHERE CATEGORY = '28609' AND FUND_PROGRAM = 'TAI TRO CHUOI HTC'
--AND BUSINESS_DATE = '20151130') X
--LEFT JOIN [ANHCP].[CEO_PERFORMANCE].DBO.SME_BRANCHCODE Y
--ON X.CO_CODE COLLATE DATABASE_DEFAULT = Y.BRANCH




------ theo hãng hàng không VNA
----LẤY DOANH SỐ BẢO LÃNH
--select distinct CUST_ID 
----into ##PS_BL 
--FROM [SERVER16_ANHCP].[VPB_WHR2].[dbo].[MD_RPT_DAILY_VPB] a 
--   where  a.FUND_PROGRAM = 'Tai tro dai ly ban ve cua HHK'
--   and a.VALUE_DATE between '20150601' and '20151117'


--   select MAX(VALUE_DATE)
----into ##PS_BL 
--FROM [SERVER16_ANHCP].[VPB_WHR2].[dbo].[MD_RPT_DAILY_VPB] a 
--   where  a.FUND_PROGRAM = 'Tai tro dai ly ban ve cua HHK'
--   and a.VALUE_DATE between '20150601' and '20151117'


--SELECT top 1 *
--FROM [SERVER16_ANHCP].[VPB_WHR2].[dbo].[VAYTHE]
--where overdraft_type ='Tai tro dai ly ban ve cua HHK'







---------------- THÊM TRƯỜNG FUND_PROGRAM


--select  *   from BAOLANH_MD_HTC

--ALTER TABLE BAOLANH_MD_HTC
--ADD FUND_PROGRAM VARCHAR(200)

--UPDATE A
--SET A.FUND_PROGRAM=B.FUND_PROGRAM
--FROM BAOLANH_MD_HTC A,  DU_NO_HTC B
--WHERE A.CUST_ID =B.CUSTOMER_ID

--UPDATE BAOLANH_MD_HTC
--SET FUND_PROGRAM ='Tai tro chuoi o to VEAM'
--WHERE FUND_PROGRAM IS NULL



--select  * from SERVER12.ANHCP.[dbo].[BAOLANH_MD_HTC]




------******------
--select  * from SERVER12.ANHCP.[dbo].[LD_DISBURSEMENT_HTC]
--select  * from LD_DISBURSEMENT_HTC

--ALTER TABLE LD_DISBURSEMENT_HTC
--ADD FUND_PROGRAM VARCHAR(200)

--UPDATE A
--SET A.FUND_PROGRAM=B.FUND_PROGRAM
--FROM LD_DISBURSEMENT_HTC A,  DU_NO_HTC B
--WHERE A.CUST_ID =B.CUSTOMER_ID

----UPDATE LD_DISBURSEMENT_HTC
----SET FUND_PROGRAM ='Tai tro chuoi o to VEAM'
----WHERE FUND_PROGRAM IS NULL

----select * from LD_DISBURSEMENT_HTC where cust_id='2614454'


				 
--select * from SERVER12.ANHCP.DBO.DU_BAOLANH_HTC

--select  * from DU_BAOLANH_HTC

--ALTER TABLE DU_BAOLANH_HTC
--ADD FUND_PROGRAM VARCHAR(200)

--UPDATE A
--SET A.FUND_PROGRAM=B.FUND_PROGRAM
--FROM DU_BAOLANH_HTC A,  DU_NO_HTC B
--WHERE A.CUST_ID =B.CUSTOMER_ID

--UPDATE DU_BAOLANH_HTC
--SET FUND_PROGRAM ='Tai tro chuoi o to VEAM'
--WHERE FUND_PROGRAM IS NULL


--select * from DU_BAOLANH_HTC
--where FUND_PROGRAM is null 
--where cust_id='2614454'

----------------------------------------------------------------

--			select * from SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG where fund_program like '%veam%'

--							select top 1 * from SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC where fund_program like '%veam%'

--				select DISTINCT fund_program from DOANH_SO_BAO_LANH_HTC where fund_program like '%veam%'

--				select DISTINCT fund_program from SERVER12.ANHCP.DBO.DU_NO_HTC where fund_program like '%veam%'
--				select * from DU_NO_HTC where fund_program like '%veam%'
